﻿Imports System.Data
Imports System.Data.OleDb
Imports geraLaudosAutomatico.conexao
Imports System.Text

Public Class destinoDAO

    Dim sql As New StringBuilder
    Dim i As Integer
    
    '-----------------------------------------------------------------------------------------
    ' CONSULTA DESTINO BY PIAZZA
    '-----------------------------------------------------------------------------------------

    'Public Function getDestinos() As Generic.List(Of destinoVO)


    '    Dim objConn As New conexao
    '    Dim destino As New destinoVO

    '    Try

    '        sql.Clear()

    '        sql.Append("SELECT DEPTO.*, DES.*, RE.* FROM (departamento DEPTO INNER JOIN regional RE ON DEPTO.id_departamento = RE.id_departamento) INNER JOIN destino DES ON DES.id_regional = RE.id_regional WHERE DES.STATUS=TRUE AND RE.STATUS=TRUE ORDER BY DES.DESC_DESTINO")




    '        Dim oReader As OleDbDataReader
    '        Dim lista As New Generic.List(Of destinoVO)
    '        Dim item As New destinoVO

    '        item.ID_DESTINO = -1
    '        item.DESC_DESTINO = "Selecione o destino do laudo"
    '        lista.Add(item)

    '        Dim oComando As New OleDbCommand(sql.ToString, objConn.abreConexao)

    '        oReader = oComando.ExecuteReader

    '        While oReader.Read

    '            destino = New destinoVO
    '            destino.ID_DESTINO = IIf(IsDBNull(oReader("ID_DESTINO")), Nothing, oReader("ID_DESTINO"))
    '            destino.DESC_DESTINO = IIf(IsDBNull(oReader("DESC_DESTINO")), Nothing, oReader("DESC_DESTINO"))
    '            destino.DESC_OFICIAL = IIf(IsDBNull(oReader("DESC_OFICIAL")), Nothing, oReader("DESC_OFICIAL"))
    '            destino.REGIONAL = IIf(IsDBNull(oReader("RE.ID_REGIONAL")), Nothing, New regionalVO With {.ID_REGIONAL = oReader("RE.ID_REGIONAL"), .NUMERO_REGIONAL = oReader("NUMERO_REGIONAL"), .CIDADE_REGIONAL = oReader("CIDADE_REGIONAL"), .DEPARTAMENTO = New departamentoVO With {.ID_DEPARTAMENTO = oReader("DEPTO.ID_DEPARTAMENTO"), .NUMERO_DEPARTAMENTO = oReader("NUMERO_DEPARTAMENTO"), .CIDADE_DEPARTAMENTO = oReader("CIDADE_DEPARTAMENTO"), .STATUS = oReader("DEPTO.STATUS")}, .STATUS = oReader("RE.STATUS")})
    '            destino.MUNICIPIO = IIf(IsDBNull(oReader("MUNICIPIO")), Nothing, oReader("MUNICIPIO"))
    '            destino.STATUS = IIf(IsDBNull(oReader("DES.STATUS")), Nothing, oReader("DES.STATUS"))
    '            destino.KM_PADRAO = (IIf(IsDBNull(oReader("KM_PADRAO")), Nothing, oReader("KM_PADRAO")))
    '            destino.DATA_CRIACAO = IIf(IsDBNull(oReader("DATA_CRIACAO")), Nothing, oReader("DATA_CRIACAO"))

    '            lista.Add(destino)

    '        End While

    '        Return lista

    '    Catch ex As Exception

    '        Throw ex

    '    Finally

    '        objConn.fechaConexao()

    '    End Try

    'End Function


    '-----------------------------------------------------------------------------------------
    ' CONSULTA DESTINO BY PIAZZA
    '-----------------------------------------------------------------------------------------

    Public Function getDestinos() As Generic.List(Of destinoVO)


        Dim objConn As New conexao
        Dim destino As New destinoVO

        Try

            sql.Clear()

            sql.Append("SELECT destino.id_destino, destino.desc_destino, destino.data_criacao, destino.desc_oficial, destino.id_regional, destino.municipio, destino.status, destino.km_padrao FROM regional INNER JOIN destino ON regional.id_regional = destino.id_regional WHERE destino.status=true AND regional.status=true ORDER BY destino.desc_destino")

            Dim oReader As OleDbDataReader
            Dim lista As New Generic.List(Of destinoVO)
            Dim item As New destinoVO

            item.ID_DESTINO = -1
            item.DESC_DESTINO = "Selecione o destino do laudo"
            lista.Add(item)

            Dim oComando As New OleDbCommand(sql.ToString, objConn.abreConexao)

            oReader = oComando.ExecuteReader

            While oReader.Read

                destino = New destinoVO
                destino.ID_DESTINO = IIf(IsDBNull(oReader("ID_DESTINO")), Nothing, oReader("ID_DESTINO"))
                destino.DESC_DESTINO = IIf(IsDBNull(oReader("DESC_DESTINO")), Nothing, oReader("DESC_DESTINO"))
                destino.DESC_OFICIAL = IIf(IsDBNull(oReader("DESC_OFICIAL")), Nothing, oReader("DESC_OFICIAL"))
                destino.REGIONAL = IIf(IsDBNull(oReader("ID_REGIONAL")), Nothing, New regionalVO With {.ID_REGIONAL = oReader("ID_REGIONAL")})
                destino.MUNICIPIO = IIf(IsDBNull(oReader("MUNICIPIO")), Nothing, oReader("MUNICIPIO"))
                destino.STATUS = IIf(IsDBNull(oReader("STATUS")), Nothing, oReader("STATUS"))
                destino.KM_PADRAO = (IIf(IsDBNull(oReader("KM_PADRAO")), Nothing, oReader("KM_PADRAO")))
                destino.DATA_CRIACAO = IIf(IsDBNull(oReader("DATA_CRIACAO")), Nothing, oReader("DATA_CRIACAO"))

                lista.Add(destino)

            End While

            Return lista

        Catch ex As Exception

            Throw ex

        Finally

            objConn.fechaConexao()

        End Try

    End Function

    '-----------------------------------------------------------------------------------------
    ' BUSCA APENAS O DESTINO DE ACORDO COM O ID. POPULA O OBJETO COM TODOS OS RELACIONAMENTOS
    '-----------------------------------------------------------------------------------------

    Public Function getDestino(ByVal id_destino As Integer) As destinoVO

        Dim destino As New destinoVO
        Dim objConn As New conexao

        sql.Clear()
        sql.Append("SELECT *FROM DESTINO WHERE ID_DESTINO=" & id_destino)

        Dim oReader As OleDbDataReader

        Try
            Dim oComando As New OleDbCommand(sql.ToString, objConn.abreConexao)

            oReader = oComando.ExecuteReader

            While oReader.Read

                 destino = New destinoVO
                destino.ID_DESTINO = oReader("ID_DESTINO")
                destino.DESC_DESTINO = oReader("DESC_DESTINO")
                destino.DESC_OFICIAL = oReader("DESC_OFICIAL")
                ' destino.REGIONAL = regional.getRegional(oReader("ID_REGIONAL"))
                destino.MUNICIPIO = oReader("MUNICIPIO")
                destino.STATUS = oReader("STATUS")

            End While

            Return destino

        Catch ex As Exception

            Throw ex

        Finally

            objConn.fechaConexao()

        End Try

    End Function

End Class
